{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2a66f154-ad7f-4afb-8ce5-fd16fabf156a",
   "metadata": {},
   "source": [
    "## Preparing Main Result Table\n",
    "\n",
    "Download the spreadsheet as is as excel file, put it inside ```data/```<br>\n",
    "Run the below cells"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "be86d649-743e-4efc-a914-d1c297ba0afb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "path = 'data/'\n",
    "\n",
    "sheet_dict = {'boom_nlu':'Boomer NLU',\n",
    "              'boom_nlg':'Boomer NLG',\n",
    "              'ft_nlu':'FT NLU Result',\n",
    "              'ft_nlg':'FT NLG Result',\n",
    "              'cls_nlg':'Classic NLG Result',\n",
    "              'zs_nlu':'ZS NLU Result',\n",
    "              'zs_nlg':'ZS NLG Result',}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f0c8bbd-86d0-4447-b6b0-301d2579266f",
   "metadata": {},
   "source": [
    "## NLU Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f4a9c36f-f9b1-42c1-a069-20e4753bb1ae",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "efcabe34-9247-48f8-8e34-6f5f6347d79d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">nusa_alinea</th>\n",
       "      <th colspan=\"2\" halign=\"left\">nusa_kalimat</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Model</th>\n",
       "      <th>emot</th>\n",
       "      <th>paragraph</th>\n",
       "      <th>topic</th>\n",
       "      <th>emot</th>\n",
       "      <th>senti</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Logistic Regression</th>\n",
       "      <td>0.782311</td>\n",
       "      <td>0.452130</td>\n",
       "      <td>0.876690</td>\n",
       "      <td>0.561770</td>\n",
       "      <td>0.749583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive Bayes</th>\n",
       "      <td>0.755076</td>\n",
       "      <td>0.377260</td>\n",
       "      <td>0.850590</td>\n",
       "      <td>0.527047</td>\n",
       "      <td>0.748863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SVM</th>\n",
       "      <td>0.763579</td>\n",
       "      <td>0.454416</td>\n",
       "      <td>0.858606</td>\n",
       "      <td>0.550790</td>\n",
       "      <td>0.760371</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    nusa_alinea                     nusa_kalimat          \n",
       "Model                      emot paragraph     topic         emot     senti\n",
       "Logistic Regression    0.782311  0.452130  0.876690     0.561770  0.749583\n",
       "Naive Bayes            0.755076  0.377260  0.850590     0.527047  0.748863\n",
       "SVM                    0.763579  0.454416  0.858606     0.550790  0.760371"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['boom_nlu']\n",
    "metric = 'macro-F1'\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',\n",
    "                      sheet_name=sheet_name)\n",
    "df = df[df['Task']!='author']\n",
    "\n",
    "df_grouped = df.groupby(['Dataset', 'Model', 'Task'])[metric].mean().reset_index()\n",
    "\n",
    "df_nusa_alinea = df_grouped[df_grouped['Dataset'] == 'nusa_alinea'].drop('Dataset', axis=1)\n",
    "df_nusa_kalimat = df_grouped[df_grouped['Dataset'] == 'nusa_kalimat'].drop('Dataset', axis=1)\n",
    "\n",
    "pivot_df_nusa_alinea = df_nusa_alinea.pivot(index='Model', columns='Task', values=metric)\n",
    "pivot_df_nusa_alinea = pivot_df_nusa_alinea.rename_axis(None)\n",
    "pivot_df_nusa_alinea.columns.name = 'Model'\n",
    "\n",
    "pivot_df_nusa_kalimat = df_nusa_kalimat.pivot(index='Model', columns='Task', values=metric)\n",
    "pivot_df_nusa_kalimat = pivot_df_nusa_kalimat.rename_axis(None)\n",
    "pivot_df_nusa_kalimat.columns.name = 'Model'\n",
    "\n",
    "df_result = pd.concat([pivot_df_nusa_alinea,pivot_df_nusa_kalimat],axis=1,keys=['nusa_alinea','nusa_kalimat']).sort_index(axis=1)\n",
    "df_list.append(df_result)\n",
    "df_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a6734301-befc-412f-80e4-6b99a77857a4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">nusa_alinea</th>\n",
       "      <th colspan=\"2\" halign=\"left\">nusa_kalimat</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>model</th>\n",
       "      <th>emot</th>\n",
       "      <th>paragraph</th>\n",
       "      <th>topic</th>\n",
       "      <th>emot</th>\n",
       "      <th>senti</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bert-base-multilingual-uncased</th>\n",
       "      <td>0.631508</td>\n",
       "      <td>0.500056</td>\n",
       "      <td>0.738157</td>\n",
       "      <td>0.441313</td>\n",
       "      <td>0.687160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indobenchmark-indobert-base-p1</th>\n",
       "      <td>0.671190</td>\n",
       "      <td>0.479226</td>\n",
       "      <td>0.858678</td>\n",
       "      <td>0.544973</td>\n",
       "      <td>0.752390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indobenchmark-indobert-large-p1</th>\n",
       "      <td>0.626457</td>\n",
       "      <td>0.317544</td>\n",
       "      <td>0.854076</td>\n",
       "      <td>0.577965</td>\n",
       "      <td>0.773980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indolem-indobert-base-uncased</th>\n",
       "      <td>0.669356</td>\n",
       "      <td>0.519337</td>\n",
       "      <td>0.848660</td>\n",
       "      <td>0.525903</td>\n",
       "      <td>0.690785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xlm-roberta-base</th>\n",
       "      <td>0.591450</td>\n",
       "      <td>0.491667</td>\n",
       "      <td>0.716806</td>\n",
       "      <td>0.470202</td>\n",
       "      <td>0.686198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xlm-roberta-large</th>\n",
       "      <td>0.674246</td>\n",
       "      <td>0.515706</td>\n",
       "      <td>0.830519</td>\n",
       "      <td>0.548372</td>\n",
       "      <td>0.790622</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                nusa_alinea                     nusa_kalimat  \\\n",
       "model                                  emot paragraph     topic         emot   \n",
       "bert-base-multilingual-uncased     0.631508  0.500056  0.738157     0.441313   \n",
       "indobenchmark-indobert-base-p1     0.671190  0.479226  0.858678     0.544973   \n",
       "indobenchmark-indobert-large-p1    0.626457  0.317544  0.854076     0.577965   \n",
       "indolem-indobert-base-uncased      0.669356  0.519337  0.848660     0.525903   \n",
       "xlm-roberta-base                   0.591450  0.491667  0.716806     0.470202   \n",
       "xlm-roberta-large                  0.674246  0.515706  0.830519     0.548372   \n",
       "\n",
       "                                           \n",
       "model                               senti  \n",
       "bert-base-multilingual-uncased   0.687160  \n",
       "indobenchmark-indobert-base-p1   0.752390  \n",
       "indobenchmark-indobert-large-p1  0.773980  \n",
       "indolem-indobert-base-uncased    0.690785  \n",
       "xlm-roberta-base                 0.686198  \n",
       "xlm-roberta-large                0.790622  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['ft_nlu']\n",
    "metric = 'macro_f1'\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',\n",
    "                      sheet_name=sheet_name)\n",
    "df = df[df['task']!='author']\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model', 'task'])[metric].mean().reset_index()\n",
    "\n",
    "df_nusa_alinea = df_grouped[df_grouped['dataset'] == 'nusa_alinea'].drop('dataset', axis=1)\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "\n",
    "pivot_df_nusa_alinea = df_nusa_alinea.pivot(index='model', columns='task', values=metric)\n",
    "pivot_df_nusa_alinea = pivot_df_nusa_alinea.rename_axis(None)\n",
    "pivot_df_nusa_alinea.columns.name = 'model'\n",
    "\n",
    "pivot_df_nusa_kalimat = df_nusa_kalimat.pivot(index='model', columns='task', values=metric)\n",
    "pivot_df_nusa_kalimat = pivot_df_nusa_kalimat.rename_axis(None)\n",
    "pivot_df_nusa_kalimat.columns.name = 'model'\n",
    "\n",
    "df_result = pd.concat([pivot_df_nusa_alinea,pivot_df_nusa_kalimat],axis=1,keys=['nusa_alinea','nusa_kalimat']).sort_index(axis=1)\n",
    "df_list.append(df_result)\n",
    "df_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e866f8ea-4cc6-48fd-a8d3-1db93c276629",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">nusa_alinea</th>\n",
       "      <th colspan=\"2\" halign=\"left\">nusa_kalimat</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>model</th>\n",
       "      <th>emot</th>\n",
       "      <th>paragraph</th>\n",
       "      <th>topic</th>\n",
       "      <th>emot</th>\n",
       "      <th>senti</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bloomz-1b1</th>\n",
       "      <td>0.117188</td>\n",
       "      <td>0.127574</td>\n",
       "      <td>0.052770</td>\n",
       "      <td>0.136376</td>\n",
       "      <td>0.606428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bloomz-1b7</th>\n",
       "      <td>0.085580</td>\n",
       "      <td>0.099193</td>\n",
       "      <td>0.127286</td>\n",
       "      <td>0.117709</td>\n",
       "      <td>0.650982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bloomz-560m</th>\n",
       "      <td>0.065747</td>\n",
       "      <td>0.116031</td>\n",
       "      <td>0.049759</td>\n",
       "      <td>0.142726</td>\n",
       "      <td>0.462248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-base</th>\n",
       "      <td>0.137566</td>\n",
       "      <td>0.076966</td>\n",
       "      <td>0.353547</td>\n",
       "      <td>0.235553</td>\n",
       "      <td>0.277031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-large</th>\n",
       "      <td>0.121845</td>\n",
       "      <td>0.074962</td>\n",
       "      <td>0.310020</td>\n",
       "      <td>0.219072</td>\n",
       "      <td>0.352473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-small</th>\n",
       "      <td>0.093527</td>\n",
       "      <td>0.086085</td>\n",
       "      <td>0.320409</td>\n",
       "      <td>0.153334</td>\n",
       "      <td>0.319222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            nusa_alinea                     nusa_kalimat          \n",
       "model              emot paragraph     topic         emot     senti\n",
       "bloomz-1b1     0.117188  0.127574  0.052770     0.136376  0.606428\n",
       "bloomz-1b7     0.085580  0.099193  0.127286     0.117709  0.650982\n",
       "bloomz-560m    0.065747  0.116031  0.049759     0.142726  0.462248\n",
       "mt0-base       0.137566  0.076966  0.353547     0.235553  0.277031\n",
       "mt0-large      0.121845  0.074962  0.310020     0.219072  0.352473\n",
       "mt0-small      0.093527  0.086085  0.320409     0.153334  0.319222"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['zs_nlu']\n",
    "metric = 'macro_f1'\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',header=[0,1],\n",
    "                      sheet_name=sheet_name)\n",
    "\n",
    "df_performance = df['mean'][metric]\n",
    "df=df[['dataset', 'task', 'model', 'lang']]\n",
    "df.columns = df.columns.droplevel(1)\n",
    "df = df.merge(df_performance, left_index=True, right_index=True)\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model', 'task'])[metric].mean().reset_index()\n",
    "\n",
    "df_nusa_alinea = df_grouped[df_grouped['dataset'] == 'nusa_alinea'].drop('dataset', axis=1)\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "\n",
    "pivot_df_nusa_alinea = df_nusa_alinea.pivot(index='model', columns='task', values=metric)\n",
    "pivot_df_nusa_alinea = pivot_df_nusa_alinea.rename_axis(None)\n",
    "pivot_df_nusa_alinea.columns.name = 'model'\n",
    "\n",
    "pivot_df_nusa_kalimat = df_nusa_kalimat.pivot(index='model', columns='task', values=metric)\n",
    "pivot_df_nusa_kalimat = pivot_df_nusa_kalimat.rename_axis(None)\n",
    "pivot_df_nusa_kalimat.columns.name = 'model'\n",
    "\n",
    "df_result = pd.concat([pivot_df_nusa_alinea,pivot_df_nusa_kalimat],axis=1,keys=['nusa_alinea','nusa_kalimat']).sort_index(axis=1)\n",
    "df_list.append(df_result)\n",
    "df_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bec835ee-8844-438f-987e-9857a9a89fcd",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">nusa_alinea</th>\n",
       "      <th colspan=\"2\" halign=\"left\">nusa_kalimat</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>emot</th>\n",
       "      <th>paragraph</th>\n",
       "      <th>topic</th>\n",
       "      <th>emot</th>\n",
       "      <th>senti</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Logistic Regression</th>\n",
       "      <td>0.782311</td>\n",
       "      <td>0.452130</td>\n",
       "      <td>0.876690</td>\n",
       "      <td>0.561770</td>\n",
       "      <td>0.749583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naive Bayes</th>\n",
       "      <td>0.755076</td>\n",
       "      <td>0.377260</td>\n",
       "      <td>0.850590</td>\n",
       "      <td>0.527047</td>\n",
       "      <td>0.748863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SVM</th>\n",
       "      <td>0.763579</td>\n",
       "      <td>0.454416</td>\n",
       "      <td>0.858606</td>\n",
       "      <td>0.550790</td>\n",
       "      <td>0.760371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bert-base-multilingual-uncased</th>\n",
       "      <td>0.631508</td>\n",
       "      <td>0.500056</td>\n",
       "      <td>0.738157</td>\n",
       "      <td>0.441313</td>\n",
       "      <td>0.687160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indobenchmark-indobert-base-p1</th>\n",
       "      <td>0.671190</td>\n",
       "      <td>0.479226</td>\n",
       "      <td>0.858678</td>\n",
       "      <td>0.544973</td>\n",
       "      <td>0.752390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indobenchmark-indobert-large-p1</th>\n",
       "      <td>0.626457</td>\n",
       "      <td>0.317544</td>\n",
       "      <td>0.854076</td>\n",
       "      <td>0.577965</td>\n",
       "      <td>0.773980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>indolem-indobert-base-uncased</th>\n",
       "      <td>0.669356</td>\n",
       "      <td>0.519337</td>\n",
       "      <td>0.848660</td>\n",
       "      <td>0.525903</td>\n",
       "      <td>0.690785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xlm-roberta-base</th>\n",
       "      <td>0.591450</td>\n",
       "      <td>0.491667</td>\n",
       "      <td>0.716806</td>\n",
       "      <td>0.470202</td>\n",
       "      <td>0.686198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xlm-roberta-large</th>\n",
       "      <td>0.674246</td>\n",
       "      <td>0.515706</td>\n",
       "      <td>0.830519</td>\n",
       "      <td>0.548372</td>\n",
       "      <td>0.790622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bloomz-1b1</th>\n",
       "      <td>0.117188</td>\n",
       "      <td>0.127574</td>\n",
       "      <td>0.052770</td>\n",
       "      <td>0.136376</td>\n",
       "      <td>0.606428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bloomz-1b7</th>\n",
       "      <td>0.085580</td>\n",
       "      <td>0.099193</td>\n",
       "      <td>0.127286</td>\n",
       "      <td>0.117709</td>\n",
       "      <td>0.650982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bloomz-560m</th>\n",
       "      <td>0.065747</td>\n",
       "      <td>0.116031</td>\n",
       "      <td>0.049759</td>\n",
       "      <td>0.142726</td>\n",
       "      <td>0.462248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-base</th>\n",
       "      <td>0.137566</td>\n",
       "      <td>0.076966</td>\n",
       "      <td>0.353547</td>\n",
       "      <td>0.235553</td>\n",
       "      <td>0.277031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-large</th>\n",
       "      <td>0.121845</td>\n",
       "      <td>0.074962</td>\n",
       "      <td>0.310020</td>\n",
       "      <td>0.219072</td>\n",
       "      <td>0.352473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mt0-small</th>\n",
       "      <td>0.093527</td>\n",
       "      <td>0.086085</td>\n",
       "      <td>0.320409</td>\n",
       "      <td>0.153334</td>\n",
       "      <td>0.319222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                nusa_alinea                     nusa_kalimat  \\\n",
       "                                       emot paragraph     topic         emot   \n",
       "Logistic Regression                0.782311  0.452130  0.876690     0.561770   \n",
       "Naive Bayes                        0.755076  0.377260  0.850590     0.527047   \n",
       "SVM                                0.763579  0.454416  0.858606     0.550790   \n",
       "bert-base-multilingual-uncased     0.631508  0.500056  0.738157     0.441313   \n",
       "indobenchmark-indobert-base-p1     0.671190  0.479226  0.858678     0.544973   \n",
       "indobenchmark-indobert-large-p1    0.626457  0.317544  0.854076     0.577965   \n",
       "indolem-indobert-base-uncased      0.669356  0.519337  0.848660     0.525903   \n",
       "xlm-roberta-base                   0.591450  0.491667  0.716806     0.470202   \n",
       "xlm-roberta-large                  0.674246  0.515706  0.830519     0.548372   \n",
       "bloomz-1b1                         0.117188  0.127574  0.052770     0.136376   \n",
       "bloomz-1b7                         0.085580  0.099193  0.127286     0.117709   \n",
       "bloomz-560m                        0.065747  0.116031  0.049759     0.142726   \n",
       "mt0-base                           0.137566  0.076966  0.353547     0.235553   \n",
       "mt0-large                          0.121845  0.074962  0.310020     0.219072   \n",
       "mt0-small                          0.093527  0.086085  0.320409     0.153334   \n",
       "\n",
       "                                           \n",
       "                                    senti  \n",
       "Logistic Regression              0.749583  \n",
       "Naive Bayes                      0.748863  \n",
       "SVM                              0.760371  \n",
       "bert-base-multilingual-uncased   0.687160  \n",
       "indobenchmark-indobert-base-p1   0.752390  \n",
       "indobenchmark-indobert-large-p1  0.773980  \n",
       "indolem-indobert-base-uncased    0.690785  \n",
       "xlm-roberta-base                 0.686198  \n",
       "xlm-roberta-large                0.790622  \n",
       "bloomz-1b1                       0.606428  \n",
       "bloomz-1b7                       0.650982  \n",
       "bloomz-560m                      0.462248  \n",
       "mt0-base                         0.277031  \n",
       "mt0-large                        0.352473  \n",
       "mt0-small                        0.319222  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_nlu = pd.concat(df_list)\n",
    "df_nlu.to_csv('data/nluresults.csv')\n",
    "df_nlu"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5198439b-49b7-42d3-8030-645ae858f6b2",
   "metadata": {},
   "source": [
    "## NLG Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c224c032-ef96-48b0-8471-e56414ebc347",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6d2d2fcf-4d3d-49d0-acb7-b89f0794352b",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model</th>\n",
       "      <th>SacreBLEU</th>\n",
       "      <th>ChrF++</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PBSMT</td>\n",
       "      <td>18.391702</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   model  SacreBLEU  ChrF++\n",
       "0  PBSMT  18.391702     NaN"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['boom_nlg']\n",
    "metric = 'bleu'\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',\n",
    "                      sheet_name=sheet_name)\n",
    "df.drop('ROUGEL', axis=1, inplace=True)\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model']).mean().reset_index()\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "df_list.append(df_nusa_kalimat)\n",
    "df_nusa_kalimat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3fd988d0-77d8-4369-af50-dac884366c1c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model</th>\n",
       "      <th>SacreBLEU</th>\n",
       "      <th>ChrF++</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>baseline-mbart</td>\n",
       "      <td>23.399874</td>\n",
       "      <td>40.322922</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>baseline-mt5</td>\n",
       "      <td>26.158898</td>\n",
       "      <td>46.836028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>indo-bart</td>\n",
       "      <td>30.877590</td>\n",
       "      <td>51.093029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>indo-gpt2</td>\n",
       "      <td>27.359663</td>\n",
       "      <td>49.247835</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            model  SacreBLEU     ChrF++\n",
       "0  baseline-mbart  23.399874  40.322922\n",
       "1    baseline-mt5  26.158898  46.836028\n",
       "2       indo-bart  30.877590  51.093029\n",
       "3       indo-gpt2  27.359663  49.247835"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['ft_nlg']\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',\n",
    "                      sheet_name=sheet_name)\n",
    "df.drop('rougel', axis=1, inplace=True)\n",
    "df = df.rename(columns={'bleu':'SacreBLEU', 'chrf':'ChrF++'})\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model']).mean().reset_index()\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "df_list.append(df_nusa_kalimat)\n",
    "df_nusa_kalimat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "de9fb641-7145-4dd5-a871-4681914da1eb",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model</th>\n",
       "      <th>SacreBLEU</th>\n",
       "      <th>ChrF++</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>copy</td>\n",
       "      <td>23.489091</td>\n",
       "      <td>41.903636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pbsmt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>word-substitution</td>\n",
       "      <td>23.802727</td>\n",
       "      <td>42.678182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               model  SacreBLEU     ChrF++\n",
       "0               copy  23.489091  41.903636\n",
       "1              pbsmt        NaN        NaN\n",
       "2  word-substitution  23.802727  42.678182"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['cls_nlg']\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',\n",
    "                      sheet_name=sheet_name)\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model']).mean().reset_index()\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "df_list.append(df_nusa_kalimat)\n",
    "df_nusa_kalimat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4261bbed-a241-4ac1-a93d-5f017241ee93",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>model</th>\n",
       "      <th>SacreBLEU</th>\n",
       "      <th>ChrF++</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bloomz-1b1</td>\n",
       "      <td>3.111779</td>\n",
       "      <td>18.341895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bloomz-1b7</td>\n",
       "      <td>5.811078</td>\n",
       "      <td>22.402549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bloomz-560m</td>\n",
       "      <td>4.399344</td>\n",
       "      <td>20.269917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mt0-base</td>\n",
       "      <td>3.231223</td>\n",
       "      <td>24.608013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>mt0-large</td>\n",
       "      <td>2.625931</td>\n",
       "      <td>23.138477</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         model  SacreBLEU     ChrF++\n",
       "0   bloomz-1b1   3.111779  18.341895\n",
       "1   bloomz-1b7   5.811078  22.402549\n",
       "2  bloomz-560m   4.399344  20.269917\n",
       "3     mt0-base   3.231223  24.608013\n",
       "4    mt0-large   2.625931  23.138477"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_name = sheet_dict['zs_nlg']\n",
    "\n",
    "df = pd.read_excel(path+'[NusaMenulis] Experiment Log.xlsx',header=[0,1],\n",
    "                      sheet_name=sheet_name)\n",
    "\n",
    "df_performance = df['mean']\n",
    "df=df[['dataset', 'task', 'model', 'src_lang', 'tgt_lang']]\n",
    "df.columns = df.columns.droplevel(1)\n",
    "df = df.merge(df_performance, left_index=True, right_index=True)\n",
    "df.drop('ROUGEL', axis=1, inplace=True)\n",
    "\n",
    "df_grouped = df.groupby(['dataset', 'model']).mean().reset_index()\n",
    "df_nusa_kalimat = df_grouped[df_grouped['dataset'] == 'nusa_kalimat'].drop('dataset', axis=1)\n",
    "df_list.append(df_nusa_kalimat)\n",
    "df_nusa_kalimat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6bca0dd2-a18f-4b7a-a472-3539bd225cd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat(df_list).reset_index(drop=True).to_csv('data/nlgresults.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfbd3a20-4b6e-4be7-b205-1c3735630513",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5730f74-e1fa-4c4d-bb72-8e7dcd59da1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "kgd",
   "language": "python",
   "name": "kgd"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
